I everyone in this elected we will learn about some important set of data tools tools that will make
your data more meaningful and help you get more information out of your data.
The first tools data sought this illiteracy rate in no particular order.
If your data has decreased it will help order it alphabetically ordered and there was alphabetic order.
If it contains numbers you can add in stem from smallest to largest or from largest to smallest that
is applied.
Data sorting on this table.
Plus Alec D column.
You want to sort with I want to arrange them according to their marks in maths in descending order.
That is the highest quarter should be first so select match column go to people dead thought and whole
menu.
You can also find this option in data menu.
Select out and then select sort.
Just the smallest you see the option of either expanding this election or combining with the same selection.
If we continue with the same selection students will stay in the same order that the student one turned
to and so on but the marks will be sorted.
This will assign wrong marks to these things to the next day.
This one's you can see that N2 in one has scored hundred marks whereas a two in one export similar marks
and maths will undo this to control the we will use the optimum expanding the selection while sorting
so go to third largest the smallest and the new expanded selection.
Start now we can see that student 4 who actually scored hundred marks is now on top whereas currently
we scored the lowest in the bottom we can similarly sought from smallest to largest.
We will talk different basis that English marks from lowest to highest.
Let's do it.
We can see students failing is now in first row with lowest marks and student 1 is in last row with
highest marks to do alphabetical ordering.
We selected table below
Gordon's Sadr entry to click on Saad a doozy.
The table is sorted alphabetically basis the content in first column to sort from you to it.
Select the other option.
You can see that the order is the worst
in this first table.
Then we start to databases that match smart student doing three.
Be Having seen mugs X saw them first
since student 2 and 3 have the same score which is a defined comment.
Patient third and fourth and excel keeps them in the same order as before.
But if you want to give another rule that is if you want to say that this should be first order basis
match score and then by saying school you can do this by using custom for go to start in Baghdad.
Select customers are
first then it to sort by match score which is already there then add a level select signed and then
in descending order like this the smallest look on.
Okay.
You can see that student 2 is now in fourth position below 20 because of lesson in marketing science.
Next we look at filtering options it will allow you to keep an adult digital you desired.
That is if you have data both male and female and we want to look at digital females only you can put
that on gender until females only.
In our example we first selected a table on which you want to apply filters.
Now you can either go to sorting through intel and select from that option or we can just use the shortcut
ordered.
Then a then de you need not President simultaneously just in order.
I e e you see these smaller triangles leaking which will give us options.
In options you can see that we can also saw table from an old
not simply port operator.
You can go and select or be easily the number shown below.
So if we want to keep only ninety nine and underscored so we need at all the other options and click
on okay.
Only these two threats I mean all the other three rows are now hidden.
You can also use the search option
if you have a large data.
You can search these particular value and click on okay to keep that particular three day.
One thing we can observe is if we apply a figure of say ninety nine hundred you can see that small triangle
has changed to a vector symbol.
This tells us that which column has actually triggered applied on IC.
Another thing to observe is if the column has numbers the option of a nuclear small triangle says no
figures.
And if the column contains text the option says X readers to let us first go through the number of filters
we select the small triangle on match marks.
There are several number of readers available.
These are self-explanatory.
You can either specify a number which equals the data or does not equal the data or you can specify
number for which the data should be greater than that particular number or less than that bottom line
number.
For our example we will use degraded in Africa.
Just click on greater than mass is greater than 80.
What this will do is it will keep the data only of student.
We have actually scored more than 18 months
similarly we can look at the text with those index filters we have fewer options which say equals or
does not equal weight.
We can give a particular string and it'll match if that string is exactly seeming are dataset the other
option it begins with arrangement where it will just check that the data in the column actually begins
with the string that we have provided.
Similarly there is a contained option which says that all those rows of data will remain which contain
the string that we have provided.
I will suggest to you to explore this on your own.
If the column contains both numbers an alphabet custom sort that can be used as it chose both options
of text filters as well as no filters for no let us just put our custom filter on mats marks only let
us go to custom filter and first we will put out greater than that of simplified marks and forget of
letting 90 marks
so only one student who has actually scored with reading say whatever I want ninety is remaining in
this data.
Other students have been given.
Next up is data validations data validation is used to prevent wrong inputs during data entry supposedly
a sheet to get a customer not to run data we want certain validation such as phone number we should
contain only numbers data but should not be older than 1920 etc..
So how to apply such validations.
Firstly the on which you want to add validation.
So if we are adding a new subject scores here we want.
This is to have numerical values between 0 100 so select this is go to data on data validation
in the allow dropdown option select whole number and mention minimum as 0 and maximum values under
econ okay.
Now whenever we we we will try adding a larger number.
It will give us some error message legacy.
Excel is giving us this area which is this value does not match deep data validation restrictions defined
for this.
We do try and get a genuine number that would be long.
Similarly for the name of subject.
If we have a fixed list of subject we can give a list validation to do this go to that particular sale
and it can be the validation nine.
Allow select list
in the source option.
You can either mention all these object list comma separated or you can go into like the list in the
tells that I created.
Okay.
Now you can see a small dropdown option in this particular city.
If you click this option you can see all these object as a draw down.
You cannot get any other value for this or let this drop down value can be selected for this.
Another take that can be applied is on the land of input characters.
For example if this to the name cannot be more than 10 characters we go to.
This will go to Data Validation allow to land at maximum value of
-- good at this.
Okay.
Now we're trying to implement those
will give us this error from any data validation options to other tabs are also valuable.
These are the input message and edit alert dad's input message will be displayed when the user is inputting
the data and error alert comes up when user has already broken the rule that we have defined let us
get to my these two messages and see what input message we give a I love
and for another look.
Title will be error in data entry and message will be
please and scored between you two and hundred you then D.
I can then you want to get in the error message.
I use these warnings saying Okay now in this cell you'll see that D.
Whenever the selected the input message will be shown and whenever you break the rule
so probably I forgot to mention the root that's going to mention the rule first allow or lumber between
zero and hundred.
Let's say give the input messages to alert.
Yes an alert is still there.
Okay.
Now let us under 110 in that.
This is the custom I say that we told to show at the data entry please and that's what we've been doing.
Andrew
next it takes two columns.
A lot of times you get comma separated or tabs separated data or just join data without any spaces.
Since keeping data like this takes up less space the files I just want and there is no limit on file
to a local.
When you are working on it in excel you want different parts of data in different columns so that you
are able to work on it.
It's a need to move this data in Table format.
Let us see how was student 6 data in that notepad file.
We can see that the data is comma separated.
If you copy paste this data in that Excel sheet.
This comes in one single cell to move this data into the right cells select the cell and go to Data
select text two columns.
You can see two options.
The embed is where the base operated by a portable separated.
So just call my head.
Six letters used where there is no separator but we know exactly the number of characters that each
column should contain so here we would use delimited option
select comma.
You can see the separating lines that illustrated for us to preview lit next we can finish.
You can see that you do that is knowing the characters could do the same for other students also.
So in flux during 7 data separated by a star symbol.
Now we again go to X two columns.
Option delimited next.
Others specify these are printed which is starred
next finish.
Okay.
This is older than similarly for the adult.
Too soon and there is no particular separator but we know the column land for each.
Each had a bitter legacy.
How X two columns six lived next.
You can either remove these separating lines to either Lane click at the desired position you can delete
a language that will leave on it you can drag and drop a blue line to move it you can next okay.
Let us know in the respective states this was X two columns.
Next is the option of removing duplicates from data.
So many times the data has seem endless several times.
We may want to remove these same entries in our data.
We can see that student 2 has 2 entries.
STUDENT 5 has to increase and student 3 onto those 2 entries.
Some of these entries at exactly same for example student.
Do you imagine marks is science and English all the same in both the entries.
So we may want to remove such duplicate.
Let us do this.
We first selected the go to data and select the more duplicate
one thing to note is that is tick box which is my data as headers.
Since our data has the heading of student Max science and English we have picked this option in case
there is no header in our data.
We can pick this option so to move.
Exact duplicate.
For example the student do that with a skirt which has seen marks in maths science and English.
We will select all the columns and click on.
OK.
We see that to duplicate have been removed and tics unique values remain
steady.
Student fine.
As to values for student fame.
We want to see only that entry in which student has scored more marks in English Let us make the email
again and we will sort this data.
By the marks in English from largest to smallest.
Now we will remove the duplicate in this data this will remove the second entry of student fame.
Which has lesser Martin English so I take all the other columns.
Just keep student name and click on okay.
That one duplicate value of two and five has been removed and you see that the one with less said Martin
English has been more than the other entry still stays that all the selector in next lecture.
We will learn about formatting options in Excel.
Thanks.